package com.itheima.dao;


import com.itheima.domain.Student;
import com.itheima.utils.JDBCUtils;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class StudentdaoImpl implements Studentdao {
    @Override
    public int add(Student student) {
        //1.获取连接
        Connection connection = JDBCUtils.getConnection();
        //2.定义sql
        String sql = "INSERT INTO student VALUES(null,?,?)";

        //3.获取执行对象
        PreparedStatement pstmt = null;
        int i = 0;
        try {
            pstmt = connection.prepareStatement(sql);
            //4.设置占位符
            pstmt.setString(1, student.getName());
            pstmt.setInt(2, student.getAge());
            //5.执行sql
            i = pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            JDBCUtils.close(connection, pstmt);
        }
        return i;
    }

    @Override
    public int update(Student student) {
        //1.获取连接
        Connection connection = JDBCUtils.getConnection();
        //2.定义sql
        String sql = "UPDATE student SET name =?,age=? where id=?";
        Student stu = new Student();
        //3.获取执行对象
        PreparedStatement pstmt = null;
        int i = 0;
        try {
            pstmt = connection.prepareStatement(sql);
            //4.设置占位符
            pstmt.setString(1, student.getName());
            pstmt.setInt(2, student.getAge());
            pstmt.setInt(3,student.getId());
            //5.执行sql
            i = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            JDBCUtils.close(connection, pstmt);
        }
        return i;
    }

    @Override
    public int deleteById(int id) {
        //1.获取连接
        Connection connection = JDBCUtils.getConnection();
        //2.定义sql
        String sql = "DELETE FROM student WHERE id = ?";

        //3.获取执行对象
        PreparedStatement pstmt = null;
        int i = 0;
        try {
            pstmt = connection.prepareStatement(sql);
            //4.设置占位符
            pstmt.setInt(1, id);
            //5.执行sql
            i = pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            JDBCUtils.close(connection, pstmt);
        }
        return i;
    }

    @Override
    public Student findById(int id) {
        //1.获取连接
        Connection connection = JDBCUtils.getConnection();
        //2.定义sql
        String sql = "SELECT * FROM student WHERE id = ?";

        //3.获取执行对象
        PreparedStatement pstmt = null;

        ResultSet rs = null;

        Student student = null;
        try {
            pstmt = connection.prepareStatement(sql);
            //4.设置占位符
            pstmt.setInt(1, id);
            //5.执行sql
            rs = pstmt.executeQuery();
            //6.遍历结果集,封装结果对象

            if (rs.next()) {
                student = new Student(rs.getInt("id"), rs.getString("name"), rs.getInt("age"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            JDBCUtils.close(connection, pstmt);
        }
        return student;
    }

    @Override
    public List<Student> findAll() {
        //1.获取连接
        Connection connection = JDBCUtils.getConnection();
        //2.定义sql
        String sql = "SELECT * FROM student";

        //3.获取执行对象
        PreparedStatement pstmt = null;

        ResultSet rs = null;

        List<Student> students = new ArrayList<>();
        try {
            pstmt = connection.prepareStatement(sql);

            //5.执行sql
            rs = pstmt.executeQuery();
            //6.遍历结果集,封装结果对象

            while (rs.next()) {
                Student student = new Student(rs.getInt("id"), rs.getString("name"), rs.getInt("age"));
                students.add(student);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            JDBCUtils.close(connection, pstmt);
        }
        return students;
    }
    }


